﻿using Org.BouncyCastle.Asn1.X509;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Zhaoxi.SqlSugar.Common;
using Zhaoxi.SqlSugar.Models;
using DbType = SqlSugar.DbType;

namespace Zhaoxi.SqlSugarAdvanced
{
    public class AdvancedFeatures
    {
        #region 生命周期 - 理论知识
        /// <summary>
        /// 生命周期 - 理论知识
        /// </summary>
        public static void LifeTimeManager()
        {
            ConnectionConfig connectionConfig = new ConnectionConfig()
            {
                ConnectionString = CustomConnectionConfig.ConnectionString001,
                IsAutoCloseConnection = true,
                DbType = DbType.SqlServer
            };
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                #region 输出Sql语句
                db.Aop.OnLogExecuting = (s, p) =>
                {
                    Console.WriteLine("----------------------------");
                    Console.WriteLine($"Sql语句:{s}");
                };
                #endregion 
                {
                    ISugarQueryable<Student> query = db.Queryable<Student>();


                    List<Student> studentList = query.ToList();
                    Student[] studentList1 = query.ToArray();

                    //ISugarQueryable 对象的内部，包含了如何生成Sql语句；--包含了生成Sql语句的规则
                    //包含了返回值；
                    //作用：在调用给你的时候，可以把查询的对象，表达式目录树。。。解析，组合成成Sql语句，但是并不会到数据库中去查询；


                    //如果在Tolist  Toarray的时候，ISugarQueryable 马上执行，开始按照规则生成Sql语句，就马上去数据库中去执行；
                    //从queryable变成了List 这个时候类型发生变化 已经生成了SQL或者执行了数据库
                }

                {
                    Console.WriteLine("===============================");
                    var able = db.Queryable<Student>();
                    able.Where(it => it.Id > 0);
                    able.Where(it => it.Id > 0);

                    var list = able.ToList();
                    //where id>0 and tid>0
                }

                {
                    var query = db.Queryable<Student>().Where(it => it.Id == 1);

                    {
                        int count = query.Count();
                        List<Student> list = query.ToList();
                        Student[] array = query.ToArray();
                        string sqlString = query.ToSqlString();
                    }

                    //{
                    //    int count = query.Clone().Count();//当query用于2个地方的时候一定要加Clone这点和EF有本质区别
                    //    var list = query.Clone().ToList();
                    //}
                }
            }
        }
        #endregion

        #region 执行Sql语句
        /// <summary>
        /// 执行Sql
        /// </summary>
        public static void ExecSql()
        {
            ConnectionConfig connectionConfig = new ConnectionConfig()
            {
                ConnectionString = CustomConnectionConfig.ConnectionString001,
                IsAutoCloseConnection = true,
                DbType = DbType.SqlServer
            };
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                #region 输出Sql语句+数据库表初始化
                db.Aop.OnLogExecuting = (s, p) =>
                {
                    Console.WriteLine("----------------------------");
                    Console.WriteLine($"Sql语句:{s}");
                };
                if (db.DbMaintenance.IsAnyTable("UinitBlukTable", false))
                {
                    db.DbMaintenance.DropTable<UinitBlukTable>();
                }
                db.CodeFirst.InitTables<UinitBlukTable>();

                #endregion

                #region 原生Sql语句操作
                {
                    //上面列表中 SqlQuery 等方法都可以不一定是GetDataTable
                    {
                        var dt = db.Ado.GetDataTable("select * from UinitBlukTable"); // sql查询表数据
                    }

                    //参数1：简化用法
                    {
                        string sql = "select * from UinitBlukTable where id=@id and name like @name";
                        var dt = db.Ado.GetDataTable(sql, new { id = 1, name = "%jack%" }); // sql查询表数据
                    }

                    //参数2：复杂用法
                    {
                        string sql = "select * from   UinitBlukTable where id=@id and name like @name";
                        var parameter = new List<SugarParameter>()
                           {
                              new SugarParameter("@id",1),
                              new SugarParameter("@name","%jack%") //执行sql语句
                           };
                        var dt = db.Ado.GetDataTable(sql, parameter); // sql查询表数据
                    }


                    {
                        //原生SQL用实体 sql 查询
                        //比db.SqlQueryable兼容性更强，支持复杂SQL存储过程，缺点没有自带的分页操作
                        List<UinitBlukTable> t1 = db.Ado.SqlQuery<UinitBlukTable>("select * from UinitBlukTable"); // sql查询表数据


                        //原生SQL用匿名对象 sql 查询
                        List<dynamic> t2 = db.Ado.SqlQuery<dynamic>("select * from UinitBlukTable"); // sql查询表数据

                        //插入 更新操作一般用
                        string sql = $"INSERT INTO [dbo].[UinitBlukTable] ([Id] ,[Name] ,[Create]) VALUES ('10000' ,'Richard' ,'{DateTime.Now.ToString()}')";
                        db.Ado.ExecuteCommand(sql); // sql插入表数据

                    }
                }
                #endregion

                #region 调用存储过程 - 存储过程很少使用 
                //调用存储过程 - 存储过程很少使用 
                {

                    //带有output的存储过程 
                    {
                        var tableName = new SugarParameter("@tableName", "Company");
                        var reFieldsStr = new SugarParameter("@ReFieldsStr", "*");
                        var orderString = new SugarParameter("@orderString", "Id");
                        var whereString = new SugarParameter("@whereString", "1=1");
                        var pageSize = new SugarParameter("@pageSize", "10");
                        var pageIndex = new SugarParameter("@PageIndex", 1);
                        var TotalRecord = new SugarParameter("@TotalRecord", null, true);//设置为output 
                        var dt = db.Ado.UseStoredProcedure().GetDataTable("SP_CustomPager", tableName, reFieldsStr, orderString, whereString, pageSize, pageIndex, TotalRecord);//返回dt 

                        //数据总条数
                        object recordCount = TotalRecord.Value;
                    }
                }
                #endregion

                #region 一次查询多个表，可以是不同的表 - 没啥用
                //查询两个结果集
                {
                    // 可以一次查询多个表，可以是不同的表 - 没啥用
                    var views = db.Ado.SqlQuery<Company, Company, Company>("select * from Company; select * from Company; select * from Company");//多实体
                    var t1list = views.Item1;
                    var t2list = views.Item2;
                }
                #endregion
            }
        }
        #endregion

        #region 导入数据+验证 - 导入报表数据时可能会用到
        /// <summary>
        /// 导入数据+验证 - 导入报表数据时可能会用到
        /// </summary>
        public static void ExportData()
        {
            ConnectionConfig connectionConfig = new ConnectionConfig()
            {
                ConnectionString = CustomConnectionConfig.ConnectionString001,
                IsAutoCloseConnection = true,
                DbType = DbType.SqlServer
            };
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                #region 输出Sql语句+数据库表初始化
                db.Aop.OnLogExecuting = (s, p) =>
                {
                    Console.WriteLine("----------------------------");
                    Console.WriteLine($"Sql语句:{s}");
                };
                if (db.DbMaintenance.IsAnyTable("UinitBlukTable", false))
                {
                    db.DbMaintenance.DropTable<UinitBlukTable>();
                }
                db.CodeFirst.InitTables<UinitBlukTable>();
                #endregion

                #region 只插入不更新
                {
                    List<UinitBlukTable> list = new List<UinitBlukTable>();
                    list.Add(new UinitBlukTable() { Id = 1, Name = "a", Create = DateTime.Now });
                    list.Add(new UinitBlukTable() { Id = 2, Name = "a", Create = DateTime.Now });
                    list.Add(new UinitBlukTable() { Id = 3, Name = "a", Create = DateTime.Now.AddYears(-2) });
                    list.Add(new UinitBlukTable() { Id = 4, Name = "", Create = DateTime.Now.AddYears(-2) });
                    {
                        db.Deleteable<UinitBlukTable>().ExecuteCommand(); //清空数据
                        var x = db.Storageable(list).SplitInsert(it => !it.Any()).ToStorage();
                        x.AsInsertable.ExecuteCommand();//插入可插入部分
                    }

                    //缩写
                    {
                        db.Deleteable<UinitBlukTable>().ExecuteCommand(); //清空数据
                        var x = db.Storageable(list) // 传入数据
                                .SplitInsert(it => !it.Any()) // 只插入不更新
                                .ToStorage() // 转换成存储操作对象
                                .AsInsertable.ExecuteCommand(); // 执行插入操作
                    }
                }
                #endregion

                #region 完整案例
                {
                    List<UinitBlukTable> list2 = new List<UinitBlukTable>();
                    list2.Add(new UinitBlukTable() { Id = 1, Name = "a", Create = DateTime.Now });
                    list2.Add(new UinitBlukTable() { Id = 2, Name = "a", Create = DateTime.Now });
                    list2.Add(new UinitBlukTable() { Id = 3, Name = "a", Create = DateTime.Now.AddYears(-2) });
                    list2.Add(new UinitBlukTable() { Id = 4, Name = "", Create = DateTime.Now.AddYears(-2) });

                    // 添加数据验证条件
                    var x = db.Storageable(list2)
                            .SplitError(it => string.IsNullOrEmpty(it.Item.Name), "名称不能为空")
                            .SplitError(it => it.Item.Create < DateTime.Now.AddYears(-1), "不是今年的数据")
                            .SplitDelete(it => it.Item.Create < DateTime.Now.AddYears(-10))//删除10年前数据
                                                                                           //.Saveable() // Saveable 等于下面2行
                            .SplitUpdate(it => it.Any())//数据库存在更新 根据主键
                            .SplitInsert(it => true)//其余没有问题的数据插入 
                            .ToStorage();
                    //输出统计
                    ////输出错误信息     
                    Console.WriteLine("插入 {0} 更新{1} 错误数据{2} 不计算数据{3} 删除数据{4},总共{5}",
                                       x.InsertList.Count,
                                       x.UpdateList.Count,
                                       x.ErrorList.Count,
                                       x.IgnoreList.Count,
                                       x.DeleteList.Count,
                                       x.TotalList.Count);
                    Console.WriteLine("====================导入数据+验证=========================");
                    foreach (var item in x.ErrorList)
                    {
                        Console.WriteLine("id等于" + item.Item.Id + " : " + item.StorageMessage);
                    }
                    // 导入数据执行验证条件
                    x.AsInsertable.ExecuteCommand(); //执行插入
                    x.AsUpdateable.ExecuteCommand(); //执行更新
                    x.AsDeleteable.ExecuteCommand(); //执行删除　　
                }
                #endregion
            }
        }
        #endregion

        #region 并发控制 - 简单了解一下 - 用的时候在深入研究
        /// <summary>
        /// 并发控制 - 简单了解一下 - 用的时候在深入研究
        /// </summary>
        public static void ConcurrencyControl()
        {
            ConnectionConfig connectionConfig = new ConnectionConfig()
            {
                ConnectionString = CustomConnectionConfig.ConnectionString001,
                IsAutoCloseConnection = true,
                DbType = DbType.SqlServer
            };
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                db.Aop.OnLogExecuting = (s, p) =>
                {
                    Console.WriteLine("----------------------------");
                    Console.WriteLine($"Sql语句:{s}");
                };
                if (db.DbMaintenance.IsAnyTable("OrderHead", false))
                {
                    db.DbMaintenance.DropTable<OrderHead>();
                }
                db.CodeFirst.InitTables<OrderHead>();
                db.Insertable<OrderHead>(new OrderHead()
                {
                    Id = 1,
                    Name = "自行车订单",
                    CreateTime = DateTime.Now,
                    Price = 3000,
                    CountInfo = 1 
                }).ExecuteCommand();

                //并发累计（累加） 
                {
                    //正确写法：安全的字段累计
                    {
                        var result = db.Updateable<OrderHead>()
                            .SetColumns(it => it.CountInfo == it.CountInfo + 1)
                            .Where(it => it.Id == 1).ExecuteCommand();
                        //sql： num=num+1

                    }
                    ////错误写法： 在程序中计算是不安全的
                    //{
                    //    OrderHead orderHead = db.Queryable<OrderHead>().First(it => it.Id == 1);
                    //    var countInfo = orderHead.CountInfo + 1;
                    //    var result = db.Updateable<OrderHead>()
                    //        .SetColumns(it => it.CountInfo == countInfo)
                    //        .Where(it => it.Id == 1).ExecuteCommand();
                    //    //sql:  num=值
                    //}
                }

                //防止提交覆盖(乐观锁)
                {
                    db.CodeFirst.InitTables<ULockEntity>();
                    db.DbMaintenance.TruncateTable<ULockEntity>();
                    db.Insertable<ULockEntity>(new ULockEntity()
                    {
                        Name = "test",
                        Ver = 1
                    }).ExecuteCommand();


                    //1、用户要打开编辑界面，然将数据绑定到编辑界面
                    //2、用户在界面填写完在点修改保存
                    //原理：打开编辑框太久别人已经修改了这条记录，我在提交就可能把别人更新数据清空掉，我们需要一个时间字段去和数据库中的Version比对
                    //重现步骤：开2个浏览器 ，编辑同一条记录， A浏览器先保存，然后B浏览器在保存就能重现
                    //因为是同一条记录本身并不存在并发，因为编辑界面打开一天也不会刷新数据，点保存一样覆盖


                    //注意：只能是实体更新不能是集合更新
                    ULockEntity uLockEntity = db.Queryable<ULockEntity>().First();
                    //【用法1：不扔错】 Ver与数据库字段不同不报错返回0
                    {
                        var rows = db.Updateable(new ULockEntity()
                        {
                            Id = uLockEntity.Id,
                            Name = "newname",
                            Ver = 1551128313597136896//版本字段会自动更新
                        }).ExecuteCommandWithOptLock();
                    }


                    //【用法2：扔出错误】Ver与数据库字段不同直接扔错出误
                    {
                        //var rows = db.Updateable(new ULockEntity()
                        //{
                        //    Id = uLockEntity.Id,
                        //    Name = "newname",
                        //    Ver = 1551128313597136896  //版本字段会自动更新
                        //}).ExecuteCommandWithOptLock(true); //加上true就会扔出错误
                        //                                    //try { ... }catch(VersionExceptions ex){...}
                        //                                    //底层 throw new VersionExceptions
                    }

                    {
                        db.CodeFirst.InitTables<ULockEntity>();
                        db.DbMaintenance.TruncateTable<ULockEntity>();
                        //第一次插入ver=0
                        var id = db.Insertable(new ULockEntity() { Name = "oldName" }).ExecuteReturnIdentity();

                        //开始用例
                        {
                            var rows = db.Updateable(new ULockEntity()
                            {
                                Id = id,
                                Name = "newname",
                                Ver = 0  //会自动更新版本字段更新后数据库将不在是0
                            }).ExecuteCommandWithOptLock();
                        }
                        //rows=1 因为数据库ver是0你传的也是0

                        {
                            var rows = db.Updateable(new ULockEntity()
                            {
                                Id = id,
                                Name = "newname2",
                                Ver = 0
                            }).ExecuteCommandWithOptLock();
                        }
                    }
                }
            }
        }
        #endregion

        #region 悲观锁 锁表操作期间别人不能用
        /// <summary>
        /// 悲观锁
        /// </summary>
        public static void PessimismLock()
        {
            ConnectionConfig connectionConfig = new ConnectionConfig()
            {
                ConnectionString = CustomConnectionConfig.ConnectionString001,
                IsAutoCloseConnection = true,
                DbType = DbType.SqlServer
            };
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                #region 输出Sql语句+数据库表初始化
                db.Aop.OnLogExecuting = (s, p) =>
                {
                    Console.WriteLine("----------------------------");
                    Console.WriteLine($"Sql语句:{s}");
                };
                #endregion

                //悲观锁等待模式 - 在操作期间锁表别人操作需要等待
                {
                    db.BeginTran();
                    //查询条件记录后锁表
                    var data = db.Queryable<Company>()
                        .TranLock(DbLockType.Wait)
                        .Where(it => it.CompanyName == "a")
                        .ToList();//返回条数尽量最少
                                  //插入、更新等操作 
                    db.CommitTran();
                }

                //悲观锁排它模式 - 在操作期间锁表别人操作会失败
                {
                    db.BeginTran();
                    //查询条件记录后锁表
                    var data = db.Queryable<Company>()
                        .TranLock(DbLockType.Error)
                        .Where(it => it.CompanyName == "a").ToList();//返回条数尽量最少
                                                                     //插入、更新等操作

                    db.CommitTran();
                }

                //测试数据
                {
                    for (int i = 0; i < 10; i++)
                    {
                        Task.Run(() =>
                        {
                            try
                            {
                                var db = GetInstance();//用的sqlsugarclient保证db线程安全每次New一下
                                db.BeginTran();
                                var getAll = db.Queryable<Company>().TranLock(DbLockType.Wait).ToList();
                                System.Threading.Thread.Sleep(1000);
                                db.CommitTran();
                                Console.WriteLine("成功");
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine("失败");
                            }
                        });
                    }
                }

                Console.Read();

            }
        }
        #endregion

        public static SqlSugarClient GetInstance()
        {
            ConnectionConfig connectionConfig = new ConnectionConfig()
            {
                ConnectionString = CustomConnectionConfig.ConnectionString001,
                IsAutoCloseConnection = true,
                DbType = DbType.SqlServer
            };
            return new SqlSugarClient(connectionConfig);
        }
    }
}
